﻿using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ReadPointsRingNumberExcel
{
    //傻逼类,还需要老子自己扩展get set cell value
    public static class HSSFSheetExtend
    {
        public static Dictionary<string, ICellStyle> styleDict = new Dictionary<string, ICellStyle>();
        public static void SetPrintRegion(this ISheet sheet, int startRow, int endRow, int startColumn, int endColumn)
        {
            string startStr = Convert.ToChar(65 + startColumn).ToString();
            string endStr = Convert.ToChar(65 + endColumn).ToString();
            sheet.Workbook.SetPrintArea(sheet.Workbook.GetSheetIndex(sheet), string.Format("{0}{1}:{2}{3}", startStr, startRow + 1, endStr, endRow + 1));
        }
        public static void SetBorder(this ISheet sheet, int startRow, int endRow, int startColumn, int endColumn)
        {
            ICellStyle style = null;
            if (styleDict.ContainsKey("BlackBorderStyle"))
            {
                style = styleDict["BlackBorderStyle"];
            }
            else
            {
                style = BlackBorderStyle(sheet);
            }

            for (int row = startRow; row <= endRow; row++)
            {
                for (int column = startColumn; column <= endColumn; column++)
                {
                    try
                    {
                        ICell cell = sheet.GetCell(row, column, true);
                        //SetBlackBorderStyle(cell.CellStyle, sheet);
                        cell.CellStyle = style;
                    }
                    catch
                    {
                        continue;
                    }
                }
            }

        }

        public static ICellStyle BlackBorderStyle(ISheet sheet, bool IsStrike = false)
        {

            ICellStyle blackBorder = sheet.Workbook.CreateCellStyle();
            blackBorder.BorderBottom = BorderStyle.Thin;
            blackBorder.BorderLeft = BorderStyle.Thin;
            blackBorder.BorderRight = BorderStyle.Thin;
            blackBorder.BorderTop = BorderStyle.Thin;

            blackBorder.Alignment = HorizontalAlignment.Center;
            blackBorder.VerticalAlignment = VerticalAlignment.Center;
            IFont font2 = blackBorder.GetFont(sheet.Workbook);
            font2.FontName = "微软雅黑";
            font2.IsStrikeout = IsStrike;
            font2.FontHeightInPoints = 9;
            blackBorder.SetFont(font2);
            styleDict.Add("BlackBorderStyle", blackBorder);
            return blackBorder;
        }
        private static ICellStyle CreateCellStyle(ISheet sheet, string fontName = "微软雅黑")
        {
            ICellStyle styleLeft = sheet.Workbook.CreateCellStyle();

            IFont font2 = sheet.Workbook.CreateFont();
            font2.FontName = fontName;
            styleLeft.SetFont(font2);
            return styleLeft;
        }
        public static void SetCell(this ISheet sheet, int row, int column, string value, string fontName = "", ICellStyle style = null)
        {

            IRow r = sheet.GetRow(row);
            if (r == null)
            {
                r = sheet.CreateRow(row);
            }
            ICell cell = r.GetCell(column, MissingCellPolicy.CREATE_NULL_AS_BLANK);
            cell.SetCellValue(value);
            if (style != null)
            {
                cell.CellStyle = style;
            }
            if (fontName != "")
            {
                cell.CellStyle = CreateCellStyle(sheet, fontName);
            }
        }
        public static void SetCell(this ISheet sheet, int row, int column, double value, ICellStyle style = null)
        {
            IRow r = sheet.GetRow(row);
            if (r == null)
            {
                r = sheet.CreateRow(row);
            }
            ICell cell = r.GetCell(column, MissingCellPolicy.CREATE_NULL_AS_BLANK);
            cell.SetCellValue(value);
            if (style != null)
            {
                cell.CellStyle = style;
            }
        }
        public static void SetCell(this ISheet sheet, int row, int column, DateTime value, ICellStyle style = null)
        {
            IRow r = sheet.GetRow(row);
            if (r == null)
            {
                r = sheet.CreateRow(row);
            }
            ICell cell = r.GetCell(column, MissingCellPolicy.CREATE_NULL_AS_BLANK);
            cell.SetCellValue(value);

            if (style != null)
            {
                cell.CellStyle = style;
            }
        }

        public static ICell GetCell(this ISheet sheet, int row, int column, bool forceCreate = false)
        {
            IRow r = sheet.GetRow(row);
            if (r == null)
            {
                if (forceCreate == true)
                {
                    r = sheet.CreateRow(row);
                }
                else
                {
                    return null;
                }
            }
            if (forceCreate == false)
            {
                ICell cell = r.GetCell(column, MissingCellPolicy.RETURN_NULL_AND_BLANK);
                return cell;
            }
            else
            {
                ICell cell = r.GetCell(column, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                return cell;
            }
        }
        public static string GetCellText(this ISheet sheet, int row, int column)
        {
            try
            {
                ICell cell = sheet.GetCell(row, column);
                if (cell != null)
                {
                    return cell.StringCellValue;
                }
                else
                {
                    return "";
                }
            }
            catch
            {
                return "";
            }
        }
        public static double GetCellNumber(this ISheet sheet, int row, int column)
        {
            try
            {
                ICell cell = sheet.GetCell(row, column);
                if (cell != null)
                {
                    return cell.NumericCellValue;
                }
                else
                {
                    return 0;
                }
            }
            catch
            {
                return 0;
            }
        }
        public static ICell Find(this ISheet sheet, string searchTxt, bool byRow = true, int maxRow = 60, int MaxColumn = 12)
        {
            ICell target = null;
            if (byRow == true)
            {
                for (int i = 0; i < maxRow; i++)
                {
                    for (int j = 0; j < MaxColumn; j++)
                    {
                        string txt = sheet.GetCellText(i, j);
                        if (txt == searchTxt)
                        {
                            target = sheet.GetCell(i, j);
                            return target;
                        }
                    }
                }
            }
            else
            {
                for (int i = 0; i < MaxColumn; i++)
                {
                    for (int j = 0; j < maxRow; j++)
                    {
                        string txt = sheet.GetCellText(j, i);
                        if (txt == searchTxt)
                        {
                            target = sheet.GetCell(j, i);
                            return target;
                        }
                    }
                }

            }
            return null;
        }
    }

    public static class HSSFCellExtend
    {
        public static ICell Offset(this ICell cell, int offRow = 0, int offColumn = 0)
        {
            ISheet sheet = cell.Sheet;
            return sheet.GetCell(cell.RowIndex + offRow, cell.ColumnIndex + offColumn, true);
        }


        public static void SetStrikeStyle(this ICell cell)
        {
            var book = cell.Sheet.Workbook;
            var sheet = cell.Sheet;
            ICellStyle style = sheet.Workbook.CreateCellStyle();
         
            IFont font2 = book.CreateFont();
            font2.FontName = "微软雅黑";
            font2.IsStrikeout = true;
            font2.FontHeightInPoints = 9;
            style.SetFont(font2);
            cell.CellStyle = style;
            //cell.CellStyle.SetFont(font2);
        }

        public static void SetCellFormat(this ICell cell, double value, string fmt)
        {
            cell.SetCellValue(value);
            ICellStyle style = null;
            if (HSSFSheetExtend.styleDict.ContainsKey(fmt))
            {
                style = HSSFSheetExtend.styleDict[fmt];
            }
            else
            {
                var hssfworkbook = cell.Sheet.Workbook;
                style = hssfworkbook.CreateCellStyle();
              
                IDataFormat format = hssfworkbook.CreateDataFormat();
                style.DataFormat = format.GetFormat(fmt);
                HSSFSheetExtend.styleDict.Add(fmt, style);
            }
            cell.CellStyle = style;
        }

        public static string GetColumnChar(this ICell cell)
        {
            var index = cell.ColumnIndex;
            int a = 65 + (index);
            char b = Convert.ToChar(a);
            return b.ToString();
        }
        public static string GetPosition(this ICell cell)
        {
            var c = cell.GetColumnChar();

            return string.Format("{0}{1}", c, cell.RowIndex + 1);
        }
    }
}
